﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using RealtorsPortal.Data_Access_Layer;
using System.Data;
using RealtorsPortal.Entities;

namespace RealtorsPortal.Business_Logic_Layer
{
    public class RegionDAL
    {
        DataAccessHelper daAccHelp = new DataAccessHelper();

        public DataTable getRegionsData()
        {
            DataTable dt = new DataTable();
            dt = daAccHelp.GetDataTable("sp_Get_Regions");
            return dt;
        }

        public DataTable getRegionsDataWithCountry()
        {
            DataTable dt = new DataTable();
            dt = daAccHelp.GetDataTable("sp_Get_Regions_With_Country_Name");
            return dt;
        }

        public int InsertRegions(Region regionObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryRegionsToInsert(regionObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Insert_Regions", pList);
        }

        public int UpdateRegions(Region regionObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryRegionsToUpdate(regionObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Update_Regions", pList);
        }

        public int DeleteRegions(Region regionObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryRegionsToDelete(regionObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Delete_Regions", pList);
        }

        public DataTable GetRegionsByRegionName(Region regionObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@RegionName", SqlDbType.NVarChar);
            para.Value = regionObj.RegionName;
            paraList.Add(para);
            dt = daAccHelp.GetData("sp_Search_By_Region", paraList);
            return dt;
        }

        public DataTable GetRegionsByRegionId(Region regionObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@RegionId", SqlDbType.NVarChar);
            para.Value = regionObj.RegionId;
            paraList.Add(para);

            dt = daAccHelp.GetData("sp_Get_Regions_By_Id", paraList);
            return dt;
        }

        public DataTable GetRegionsByCountryId(Region regionObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@CountryId", SqlDbType.Int);
            para.Value = regionObj.CountryId;
            paraList.Add(para);

            dt = daAccHelp.GetData("sp_Get_Regions_By_CountryId", paraList);
            return dt;
        }

        public DataTable GetAllByRegions(Region regionObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@RegionName", SqlDbType.NVarChar);
            para.Value = regionObj.RegionName;
            paraList.Add(para);

            dt = daAccHelp.GetData("sp_Get_All_By_Regions", paraList);
            return dt;
        }

        private List<SqlParameter> ExecuteNonQueryRegionsToInsert(Region regionObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@RegionName", SqlDbType.NVarChar);
            para.Value = regionObj.RegionName;
            paraList.Add(para);
            para = new SqlParameter("@CountryId", SqlDbType.NVarChar);
            para.Value = regionObj.CountryId;
            paraList.Add(para);

            return paraList;
        }
        
        private List<SqlParameter> ExecuteNonQueryRegionsToDelete(Region regionObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@RegionId", SqlDbType.NVarChar);
            para.Value = regionObj.RegionId;
            paraList.Add(para);
            
            return paraList;
        }

        private List<SqlParameter> ExecuteNonQueryRegionsToUpdate(Region regionObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@RegionId", SqlDbType.Int);
            para.Value = regionObj.RegionId;
            paraList.Add(para);
            para = new SqlParameter("@RegionName", SqlDbType.NVarChar);
            para.Value = regionObj.RegionName;
            paraList.Add(para);
            para = new SqlParameter("@CountryId", SqlDbType.Int);
            para.Value = regionObj.CountryId;
            paraList.Add(para);
            
            return paraList;
        }
    }
}